const db = require("../model/database");
const sqlQuery = require("../model/query");

// 获取年级列表
exports.getClassList = async (req, res) => {
    const sql = "SELECT * FROM class WHERE is_delete = 0";
    const result = await sqlQuery(sql, null, db);
    // 获取各年级的学生数量
    const sql1 =
        "SELECT class, COUNT(*) AS student_count FROM student WHERE is_delete = 0 AND TIMESTAMPDIFF(DAY, student.create_time, CURDATE()) <= 3 * 365 GROUP BY class";
    const result1 = await sqlQuery(sql1, null, db);
    // 给年级添加学生数量
    result.forEach((item) => {
        result1.forEach((item1) => {
            if (item.id === item1.class) {
                item.student = item1.student_count;
            }
        });
        if (!item.student) {
            item.student = 0;
        }
    });

    if (result.length > 0) {
        res.json({
            code: 20000,
            data: result
        });
    } else {
        res.json({
            code: 20000,
            msg: "暂无数据"
        });
    }
};

// 添加年级
exports.addClass = async (req, res) => {
    const classInfo = req.body;
    // 检查年级是否存在
    const sql1 = "SELECT * FROM class WHERE name = ?";
    const result1 = await sqlQuery(sql1, [classInfo.name], db);
    if (result1.length > 0) {
        res.json({
            code: 40001,
            msg: "年级已存在"
        });
        return;
    }

    const sql = "INSERT INTO class set?";
    const result = await sqlQuery(sql, [classInfo], db);
    // 更新教师表中的年级id
    const sql2 = "UPDATE teacher set class_id = ? WHERE id = ?";
    await sqlQuery(sql2, [result.insertId, classInfo.master_id], db);

    if (result.affectedRows > 0) {
        res.json({
            code: 20000,
            msg: "添加成功"
        });
    } else {
        res.json({
            code: 40001,
            msg: "添加失败"
        });
    }
};

// 修改年级
exports.updateClass = async (req, res) => {
    const classInfo = req.body;
    // 检查年级是否存在
    const sql1 = "SELECT * FROM class WHERE name = ? and id != ?";
    const result1 = await sqlQuery(sql1, [classInfo.name, classInfo.id], db);
    if (result1.length > 0) {
        res.json({
            code: 40001,
            msg: "年级已存在"
        });
        return;
    }

    // 删除多余的键值对
    delete classInfo.student;
    const sql = "UPDATE class set? WHERE id = ?";
    const result = await sqlQuery(sql, [classInfo, classInfo.id], db);
    // 一个教师只能带一个年级，检查是否有其他年级
    const sql2 = "SELECT * FROM class WHERE master_id = ? and id != ?";
    const result2 = await sqlQuery(sql2, [classInfo.master_id, classInfo.id], db);
    if (result2.length > 0) {
        // 有其他年级，删除原来的年级id
        res.json({
            code: 40001,
            msg: "一个老师只能是一个班的班主任"
        });
        return;
    }
    // 将原来教师的年级id删除
    const sql4 = "UPDATE teacher set class_id = null WHERE class_id = ?";
    await sqlQuery(sql4, [classInfo.id], db);
    // 更新教师表中的年级id
    const sql3 = "UPDATE teacher set class_id = ? WHERE id = ?";
    await sqlQuery(sql3, [classInfo.id, classInfo.master_id], db);

    if (result.affectedRows > 0) {
        res.json({
            code: 20000,
            msg: "修改成功"
        });
    } else {
        res.json({
            code: 40001,
            msg: "修改失败"
        });
    }
};

// 删除年级
exports.deleteClass = async (req, res) => {
    const id = req.params.id;
    if (!id) {
        res.json({
            code: 40001,
            msg: "参数错误"
        });
        return;
    }
    // 当有学生存在时，不允许删除 因为外键约束
    const sql1 = "SELECT * FROM student WHERE is_delete=0 and class = ?";
    const result1 = await sqlQuery(sql1, [id], db);
    if (result1.length > 0) {
        res.json({
            code: 40001,
            msg: "年级中有学生，不允许删除"
        });
        return;
    }
    // 当有老师存在时，清除老师的年级id
    const sql2 = "UPDATE teacher set class_id = null WHERE class_id = ?";
    await sqlQuery(sql2, [id], db);

    // 真实删除
    const sql = "DELETE FROM class WHERE id = ?";
    const result = await sqlQuery(sql, [id], db);
    if (result.affectedRows > 0) {
        res.json({
            code: 20000,
            msg: "删除成功"
        });
    } else {
        res.json({
            code: 40001,
            msg: "删除失败"
        });
    }
};

// 更新年级中老师的名字
exports.updateTeacherName = async (req, res) => {
    // 查询class表中的老师id
    const sql = "SELECT master_id FROM class WHERE is_delete = 0";
    const result = await sqlQuery(sql, null, db);
    // 查询老师表中的老师名字
    const sql1 = "SELECT id, name FROM teacher WHERE is_delete = 0";
    const result1 = await sqlQuery(sql1, null, db);
    // 更新class表中的老师名字
    result.forEach((item) => {
        result1.forEach((item1) => {
            if (item.master_id === item1.id) {
                const sql2 = "UPDATE class set master_name = ? WHERE master_id = ?";
                sqlQuery(sql2, [item1.name, item.master_id], db);
            }
        });
    });
    res.json({
        code: 20000,
        msg: "更新成功"
    });
};
